/************************************************************************************/
/*  Program name CAPBREXT_COMMON.sql                                                */
/*  Creater:  WONG Wing Kin (PCCW - UniHub)                                         */
/*************   History   *************************                                */
/* 30 NOV, 2005   WONG Wing Kin         First created                               */
/************************************************************************************/

SET DEFINE OFF;

CREATE OR REPLACE PACKAGE CAPBREXT_COMMON
IS
	FUNCTION GetUser(
		pUserID			NUMBER
	) RETURN VARCHAR2;

	FUNCTION GetSegment(
		pCCID			NUMBER
	) RETURN VARCHAR2;

	FUNCTION GetLocation(
		pLocID			NUMBER
	) RETURN VARCHAR2;

	FUNCTION GetAPTerm(
		pTermID			NUMBER
	) RETURN VARCHAR2;

	FUNCTION GetBankName(
		pBankBranchID	NUMBER
	) RETURN VARCHAR2;

	FUNCTION GetOrgName(
		pOrgID			NUMBER
	) RETURN VARCHAR2;

	FUNCTION GetPOVendor(
		pVendID			NUMBER
	) RETURN VARCHAR2;

	FUNCTION GetPOVendorSite(
		pVendSiteID		NUMBER
	) RETURN VARCHAR2;

	FUNCTION GetChkForPayMethod(
		pChkFormatID	NUMBER
	) RETURN VARCHAR2;

	FUNCTION GetBankACNum(
		pOrgID			NUMBER,
		pBankACID		NUMBER
	) RETURN VARCHAR2;

	FUNCTION GetParentValueSet(
		pParentVSetID	NUMBER
	) RETURN VARCHAR2;

	FUNCTION GetPerNationality(
		pPerNatCode		VARCHAR2
	) RETURN VARCHAR2;

	FUNCTION GetBGName(
		pBGID			NUMBER
	) RETURN VARCHAR2;

	FUNCTION GetBG_LE(
		pBGID			NUMBER
	) RETURN VARCHAR2;

	FUNCTION GetAppName(
		pAppID			NUMBER
	) RETURN VARCHAR2;

	FUNCTION GetRespKey(
		pRespID			NUMBER
	) RETURN VARCHAR2;

	FUNCTION GetMenuName(
		pMenuID			NUMBER
	) RETURN VARCHAR2;

	FUNCTION GetFormFunc(
		pFormFuncID		NUMBER
	) RETURN VARCHAR2;

	FUNCTION GetDocSeqName(
		pDocSeqID		NUMBER
	) RETURN VARCHAR2;

	FUNCTION GetSOBShortName(
		pSOB_ID			NUMBER
	) RETURN VARCHAR2;

	FUNCTION GetVendorName(
		pVendor_ID		NUMBER
	) RETURN VARCHAR2;

	FUNCTION GetVendorNum(
		pVendor_ID		NUMBER
	) RETURN VARCHAR2;

	FUNCTION GetVendorSiteCode(
		pVendor_ID		NUMBER,
		pVendorSite_ID	NUMBER
	) RETURN VARCHAR2;

	FUNCTION GetInvoiceNumber(
		pInvoice_ID		NUMBER
	) RETURN VARCHAR2;

	FUNCTION GetBatchName(
		pBatch_ID		NUMBER
	) RETURN VARCHAR2;

	FUNCTION GetEncumType(
		pEncumTypeID	NUMBER
	) RETURN VARCHAR2;

	FUNCTION GetBudgetName(
		pBudgetVerID	NUMBER
	) RETURN VARCHAR2;

	PROCEDURE GetPOInfo(
		pPO_DistID		IN	NUMBER,
		pPO_Num			OUT	VARCHAR2,
		pPO_LineNum		OUT	NUMBER,
		pPO_DistNum		OUT	NUMBER
	);

	PROCEDURE GetRCVTransInfo(
		pRCV_TransID	IN	NUMBER,
		pRCV_OrgName	OUT	VARCHAR2,
		pRCV_RecNum		OUT	VARCHAR2,
		pRCV_Qty		OUT	NUMBER
	);

	PROCEDURE GetExtBankAcc(
		pExtBankID		IN	NUMBER,
		pBank_Name		OUT	VARCHAR2,
		pBank_AccName	OUT	VARCHAR2
	);

END CAPBREXT_COMMON;
/
SET DEFINE OFF;

CREATE OR REPLACE PACKAGE BODY CAPBREXT_COMMON
IS
/********************************************************/
	FUNCTION GetUser(
		pUserID		NUMBER
	) RETURN VARCHAR2 IS
		lv_User		VARCHAR2(100);
	BEGIN
		IF	pUserID	IS	NOT NULL	THEN
			SELECT	User_Name
			INTO	lv_User
			FROM	FND_USER
			WHERE	User_ID	=	pUserID;
		ELSE
			lv_User	:=	NULL;
		END IF;
		RETURN(lv_User);
	END GetUser;
/********************************************************/
	FUNCTION GetSegment(
		pCCID		NUMBER
	) RETURN VARCHAR2 IS
		lv_Segment		VARCHAR2(100);
	BEGIN
		IF	pCCID	IS	NOT NULL	THEN
			SELECT	Segment1||'.'||Segment2||'.'||Segment3||'.'||Segment4||'.'||Segment5||'.'||Segment6||'.'||Segment7||'.'||Segment8
			INTO	lv_Segment
			FROM	GL_CODE_COMBINATIONS
			WHERE	Code_Combination_ID	=	pCCID;
		ELSE
			lv_Segment	:=	NULL;
		END IF;
		RETURN(lv_Segment);
	END GetSegment;
/********************************************************/
	FUNCTION GetLocation(
		pLocID		NUMBER
	) RETURN VARCHAR2 IS
		lv_Location		VARCHAR2(60);
	BEGIN
		IF	pLocID	IS	NOT NULL	THEN
			SELECT	Location_Code
			INTO	lv_Location
			FROM	HR_Locations
			WHERE	Location_ID	=	pLocID;
		ELSE
			lv_Location	:=	NULL;
		END IF;
		RETURN(lv_Location);
	END GetLocation;
/********************************************************/
	FUNCTION GetAPTerm(
		pTermID		NUMBER
	) RETURN VARCHAR2 IS
		lv_Terms		VARCHAR2(50);
	BEGIN
		IF	pTermID	IS	NOT NULL	THEN
			SELECT	Name
			INTO	lv_Terms
			FROM	AP_Terms
			WHERE	Term_ID	=	pTermID;
		ELSE
			lv_Terms	:=	NULL;
		END IF;
		RETURN(lv_Terms);
	END GetAPTerm;
/********************************************************/
	FUNCTION GetBankName(
		pBankBranchID		NUMBER
	) RETURN VARCHAR2 IS
		lv_BankName		VARCHAR2(60);
	BEGIN
		IF	pBankBranchID	IS	NOT NULL	THEN
			SELECT	Bank_Name
			INTO	lv_BankName
			FROM	AP_Bank_Branches
			WHERE	Bank_Branch_ID	=	pBankBranchID;
		ELSE
			lv_BankName	:=	NULL;
		END IF;
		RETURN(lv_BankName);
	END GetBankName;
/********************************************************/
	FUNCTION GetOrgName(
		pOrgID		NUMBER
	) RETURN VARCHAR2 IS
		lv_OrgName		VARCHAR2(240);
	BEGIN
		IF	pOrgID	IS	NOT NULL	THEN
			SELECT	Name
			INTO	lv_OrgName
			FROM	HR_All_Organization_Units
			WHERE	Organization_ID	=	pOrgID;
		ELSE
			lv_OrgName	:=	NULL;
		END IF;
		RETURN(lv_OrgName);
	END GetOrgName;
/********************************************************/
	FUNCTION GetPOVendor(
		pVendID			NUMBER
	) RETURN VARCHAR2 IS
		lv_VendName		VARCHAR2(240);
	BEGIN
		IF	pVendID	IS	NOT NULL	THEN
			SELECT	Vendor_Name
			INTO	lv_VendName
			FROM	PO_VENDORS
			WHERE	Vendor_ID	=	pVendID;
		ELSE
			lv_VendName	:=	NULL;
		END IF;
		RETURN(lv_VendName);
	END GetPOVendor;
/********************************************************/
	FUNCTION GetPOVendorSite(
		pVendSiteID		NUMBER
	) RETURN VARCHAR2 IS
		lv_VendSite		VARCHAR2(15);
	BEGIN
		IF	pVendSiteID	IS	NOT NULL	THEN
			SELECT	Vendor_Site_Code
			INTO	lv_VendSite
			FROM	PO_VENDOR_SITES_ALL
			WHERE	Vendor_Site_ID	=	pVendSiteID;
		ELSE
			lv_VendSite	:=	NULL;
		END IF;
		RETURN(lv_VendSite);
	END GetPOVendorSite;
/********************************************************/
	FUNCTION GetChkForPayMethod(
		pChkFormatID	NUMBER
	) RETURN VARCHAR2 IS
		lv_PayMethod		VARCHAR2(25);
	BEGIN
		IF	pChkFormatID	IS	NOT NULL	THEN
			SELECT	Payment_Method_Lookup_Code
			INTO	lv_PayMethod
			FROM	AP_CHECK_FORMATS
			WHERE	Check_Format_ID	=	pChkFormatID;
		ELSE
			lv_PayMethod	:=	NULL;
		END IF;
		RETURN(lv_PayMethod);
	END GetChkForPayMethod;
/********************************************************/
	FUNCTION GetBankACNum(
		pOrgID		NUMBER,
		pBankACID	NUMBER
	) RETURN VARCHAR2 IS
		lv_BankACNum		VARCHAR2(30);
	BEGIN
		IF	pBankACID	IS	NOT NULL	THEN
			SELECT	Bank_Account_Num
			INTO	lv_BankACNum
			FROM	AP_BANK_ACCOUNTS_ALL
			WHERE	Org_ID			=	pOrgID
			AND		Bank_Account_ID	=	pBankACID;
		ELSE
			lv_BankACNum	:=	NULL;
		END IF;
		RETURN(lv_BankACNum);
	END GetBankACNum;
/********************************************************/
	FUNCTION GetParentValueSet(
		pParentVSetID	NUMBER
	) RETURN VARCHAR2 IS
		lv_ValueSetName		VARCHAR2(60);
	BEGIN
		IF	pParentVSetID	IS	NOT NULL	THEN
			SELECT	Flex_Value_Set_Name
			INTO	lv_ValueSetName
			FROM	FND_FLEX_VALUE_SETS
			WHERE	Flex_Value_Set_ID	=	pParentVSetID;
		ELSE
			lv_ValueSetName	:=	NULL;
		END IF;
		RETURN(lv_ValueSetName);
	END GetParentValueSet;
/********************************************************/
	FUNCTION GetPerNationality(
		pPerNatCode		VARCHAR2
	) RETURN VARCHAR2 IS
		lv_Meaning			VARCHAR2(80);
	BEGIN
		IF	pPerNatCode	IS	NOT NULL	THEN
			SELECT Meaning
			INTO   lv_Meaning
			FROM   FND_Common_Lookups
			WHERE  Application_ID		=	800
			AND    UPPER(Lookup_Type)	=	'NATIONALITY'
			AND    Lookup_Code			=	pPerNatCode;
		ELSE
			lv_Meaning	:=	NULL;
		END IF;
		RETURN(lv_Meaning);
	END GetPerNationality;
/********************************************************/
	FUNCTION GetBG_LE(
		pBGID	NUMBER
	) RETURN VARCHAR2 IS
		lv_LE_Name		VARCHAR2(240);
	BEGIN
		IF	pBGID	IS	NOT NULL	THEN
			SELECT	O.Name
			INTO	lv_LE_Name
			FROM
				HR_ALL_ORGANIZATION_UNITS	O,
				HR_ORGANIZATION_INFORMATION	I
			WHERE	O.Business_Group_ID			=	pBGID
			AND		O.Organization_ID			=	I.Organization_ID
			AND		I.Org_Information_Context	=	'Legal Entity Accounting';
		ELSE
			lv_LE_Name	:=	NULL;
		END IF;
		RETURN(lv_LE_Name);
	END GetBG_LE;
/********************************************************/
	FUNCTION GetBGName(
		pBGID			NUMBER
	) RETURN VARCHAR2 IS
		lv_BG_Name		VARCHAR2(240);
	BEGIN
		IF	pBGID	IS	NOT NULL	THEN
			SELECT	Name
			INTO	lv_BG_Name
			FROM	PER_BUSINESS_GROUPS
			WHERE	Business_Group_ID			=	pBGID;
		ELSE
			lv_BG_Name	:=	NULL;
		END IF;
		RETURN(lv_BG_Name);
	END GetBGName;
/********************************************************/
	FUNCTION GetAppName(
		pAppID			NUMBER
	) RETURN VARCHAR2 IS
		lv_AppName		VARCHAR2(240);
	BEGIN
		IF	pAppID	IS	NOT NULL	THEN
			SELECT	Application_Name
			INTO	lv_AppName
			FROM	FND_APPLICATION_VL
			WHERE	Application_ID	=	pAppID;
		ELSE
			lv_AppName	:=	NULL;
		END IF;
		RETURN(lv_AppName);
	END GetAppName;
/********************************************************/
	FUNCTION GetRespKey(
		pRespID			NUMBER
	) RETURN VARCHAR2 IS
		lv_RespKey		VARCHAR2(30);
	BEGIN
		IF	pRespID	IS	NOT NULL	THEN
			SELECT	Responsibility_Key
			INTO	lv_RespKey
			FROM	FND_RESPONSIBILITY_VL
			WHERE	Responsibility_ID	=	pRespID;
		ELSE
			lv_RespKey	:=	NULL;
		END IF;
		RETURN(lv_RespKey);
	END GetRespKey;
/********************************************************/
	FUNCTION GetMenuName(
		pMenuID			NUMBER
	) RETURN VARCHAR2 IS
		lv_MenuName		VARCHAR2(30);
	BEGIN
		IF	pMenuID	IS	NOT NULL	THEN
			SELECT	Menu_Name
			INTO	lv_MenuName
			FROM	FND_MENUS
			WHERE	Menu_ID	=	pMenuID;
		ELSE
			lv_MenuName	:=	NULL;
		END IF;
		RETURN(lv_MenuName);
	END GetMenuName;
/********************************************************/
	FUNCTION GetFormFunc(
		pFormFuncID		NUMBER
	) RETURN VARCHAR2 IS
		lv_FuncName		VARCHAR2(30);
	BEGIN
		IF	pFormFuncID	IS	NOT NULL	THEN
			SELECT	Function_Name
			INTO	lv_FuncName
			FROM	FND_FORM_FUNCTIONS
			WHERE	Function_ID	=	pFormFuncID;
		ELSE
			lv_FuncName	:=	NULL;
		END IF;
		RETURN(lv_FuncName);
	END GetFormFunc;
/********************************************************/
	FUNCTION GetDocSeqName(
		pDocSeqID		NUMBER
	) RETURN VARCHAR2 IS
		lv_DocSeqName		VARCHAR2(30);
	BEGIN
		IF	pDocSeqID	IS	NOT NULL	THEN
			SELECT	Name
			INTO	lv_DocSeqName
			FROM	FND_DOCUMENT_SEQUENCES
			WHERE	Doc_Sequence_ID	=	pDocSeqID;
		ELSE
			lv_DocSeqName	:=	NULL;
		END IF;
		RETURN(lv_DocSeqName);
	END GetDocSeqName;
/********************************************************/
	FUNCTION GetSOBShortName(
		pSOB_ID			NUMBER
	) RETURN VARCHAR2 IS
		lv_SOBSName		VARCHAR2(20);
	BEGIN
		IF	pSOB_ID	IS	NOT NULL	THEN
			SELECT	Short_Name
			INTO	lv_SOBSName
			FROM	GL_SETS_OF_BOOKS
			WHERE	Set_Of_Books_ID	=	pSOB_ID;
		ELSE
			lv_SOBSName	:=	NULL;
		END IF;
		RETURN(lv_SOBSName);
	END GetSOBShortName;
/********************************************************/
	FUNCTION GetVendorName(
		pVendor_ID		NUMBER
	) RETURN VARCHAR2 IS
		lv_VendName		VARCHAR2(240);
	BEGIN
		IF	pVendor_ID	IS	NOT NULL	THEN
			SELECT	Vendor_Name
			INTO	lv_VendName
			FROM	PO_VENDORS
			WHERE	Vendor_ID	=	pVendor_ID;
		ELSE
			lv_VendName	:=	NULL;
		END IF;
		RETURN(lv_VendName);
	END GetVendorName;
/********************************************************/
	FUNCTION GetVendorNum(
		pVendor_ID		NUMBER
	) RETURN VARCHAR2 IS
		lv_VendNum		VARCHAR2(30);
	BEGIN
		IF	pVendor_ID	IS	NOT NULL	THEN
			SELECT	Segment1
			INTO	lv_VendNum
			FROM	PO_VENDORS
			WHERE	Vendor_ID	=	pVendor_ID;
		ELSE
			lv_VendNum	:=	NULL;
		END IF;
		RETURN(lv_VendNum);
	END GetVendorNum;
/********************************************************/
	FUNCTION GetVendorSiteCode(
		pVendor_ID		NUMBER,
		pVendorSite_ID	NUMBER
	) RETURN VARCHAR2 IS
		lv_VendSiteCode		VARCHAR2(15);
	BEGIN
		IF	pVendorSite_ID	IS	NOT NULL	THEN
			SELECT	Vendor_Site_Code
			INTO	lv_VendSiteCode
			FROM	PO_VENDOR_SITES_ALL
			WHERE	Vendor_ID		=	pVendor_ID
			AND		Vendor_Site_ID	=	pVendorSite_ID;
		ELSE
			lv_VendSiteCode	:=	NULL;
		END IF;
		RETURN(lv_VendSiteCode);
	END GetVendorSiteCode;
/********************************************************/
	FUNCTION GetInvoiceNumber(
		pInvoice_ID		NUMBER
	) RETURN VARCHAR2 IS
		lv_InvoiceNum		VARCHAR2(50);
	BEGIN
		IF	pInvoice_ID	IS	NOT NULL	THEN
			SELECT	Invoice_Num
			INTO	lv_InvoiceNum
			FROM	AP_INVOICES_ALL
			WHERE	Invoice_ID		=	pInvoice_ID;
		ELSE
			lv_InvoiceNum	:=	NULL;
		END IF;
		RETURN(lv_InvoiceNum);
	END GetInvoiceNumber;
/********************************************************/
	FUNCTION GetBatchName(
		pBatch_ID		NUMBER
	) RETURN VARCHAR2 IS
		lv_BatchName		VARCHAR2(50);
	BEGIN
		IF	pBatch_ID	IS	NOT NULL	THEN
			SELECT	Batch_Name
			INTO	lv_BatchName
			FROM	AP_BATCHES_ALL
			WHERE	Batch_ID		=	pBatch_ID;
		ELSE
			lv_BatchName	:=	NULL;
		END IF;
		RETURN(lv_BatchName);
	END GetBatchName;
/********************************************************/
	FUNCTION GetEncumType(
		pEncumTypeID	NUMBER
	) RETURN VARCHAR2 IS
		lv_EncumType		VARCHAR2(30);
	BEGIN
		IF	pEncumTypeID	IS	NOT NULL	THEN
			SELECT	Encumbrance_Type
			INTO	lv_EncumType
			FROM	GL_ENCUMBRANCE_TYPES
			WHERE	Encumbrance_Type_ID		=	pEncumTypeID;
		ELSE
			lv_EncumType	:=	NULL;
		END IF;
		RETURN(lv_EncumType);
	END GetEncumType;
/********************************************************/
	FUNCTION GetBudgetName(
		pBudgetVerID	NUMBER
	) RETURN VARCHAR2 IS
		lv_BudgetName		VARCHAR2(15);
	BEGIN
		IF	pBudgetVerID	IS	NOT NULL	THEN
			SELECT	Budget_Name
			INTO	lv_BudgetName
			FROM	GL_BUDGET_VERSIONS
			WHERE	Budget_Version_ID		=	pBudgetVerID;
		ELSE
			lv_BudgetName	:=	NULL;
		END IF;
		RETURN(lv_BudgetName);
	END GetBudgetName;
/********************************************************/
	PROCEDURE GetPOInfo(
		pPO_DistID		IN	NUMBER,
		pPO_Num			OUT	VARCHAR2,
		pPO_LineNum		OUT	NUMBER,
		pPO_DistNum		OUT	NUMBER
	) IS
	BEGIN
		IF	pPO_DistID	IS	NOT NULL	THEN
			SELECT
				A.Segment1,
				B.Line_Num,
				C.Distribution_Num
			INTO
				pPO_Num,
				pPO_LineNum,
				pPO_DistNum
			FROM
				PO_HEADERS_ALL			A,
				PO_LINES_ALL			B,
				PO_DISTRIBUTIONS_ALL	C
			WHERE	C.PO_Distribution_ID	=	pPO_DistID
			AND		C.PO_Line_ID			=	B.PO_Line_ID
			AND		C.PO_Header_ID			=	A.PO_Header_ID;
		ELSE
			pPO_Num		:=	NULL;
			pPO_LineNum	:=	NULL;
			pPO_DistNum	:=	NULL;
		END IF;
	END GetPOInfo;
/********************************************************/
	PROCEDURE GetRCVTransInfo(
		pRCV_TransID	IN	NUMBER,
		pRCV_OrgName	OUT	VARCHAR2,
		pRCV_RecNum		OUT	VARCHAR2,
		pRCV_Qty		OUT	NUMBER
	) IS
	BEGIN
		IF	pRCV_TransID	IS	NOT NULL	THEN
			SELECT
				C.Name,
				B.Receipt_Num,
				A.Quantity
			INTO
				pRCV_OrgName,
				pRCV_RecNum,
				pRCV_Qty
			FROM
				RCV_TRANSACTIONS			A,
				RCV_SHIPMENT_HEADERS		B,
				HR_ALL_ORGANIZATION_UNITS	C
			WHERE	A.Transaction_ID		=	pRCV_TransID
			AND		A.Shipment_Header_ID	=	B.Shipment_Header_ID
			AND		A.Organization_ID		=	B.Ship_To_Org_ID
			AND		B.Ship_To_Org_ID		=	C.Organization_ID;
		ELSE
			pRCV_OrgName	:=	NULL;
			pRCV_RecNum		:=	NULL;
			pRCV_Qty		:=	NULL;
		END IF;
	END GetRCVTransInfo;
/********************************************************/
	PROCEDURE GetExtBankAcc(
		pExtBankID		IN	NUMBER,
		pBank_Name		OUT	VARCHAR2,
		pBank_AccName	OUT	VARCHAR2
	) IS
	BEGIN
		IF	pExtBankID	IS	NOT NULL	THEN
			SELECT
				B.Bank_Name,
				A.Bank_Account_Name
			INTO
				pBank_Name,
				pBank_AccName
			FROM
				AP_BANK_ACCOUNTS_ALL	A,
				AP_BANK_BRANCHES		B
			WHERE	A.Bank_Account_ID	=	pExtBankID
			AND		A.Bank_Branch_ID	=	B.Bank_Branch_ID
			AND		A.Account_Type		=	'SUPPLIER';
		ELSE
			pBank_Name		:=	NULL;
			pBank_AccName	:=	NULL;
		END IF;
	END GetExtBankAcc;
/********************************************************/
END CAPBREXT_COMMON;
/
